var express = require("express");
var router = express.Router();
const { successResponse, errorResponse } = require("../utils/response");
const { QueryTypes } = require("sequelize");
const db = require("../mysql/db").promise();
const dayjs = require("dayjs");

// 统一日期处理中间件
const handleDateRange = (req, res, next) => {
  const { startDate = "", endDate = "" } = req.query;
  // console.log('123')
  req.dateCondition = {
    start:
      startDate || dayjs().subtract(30, "days").format("YYYY-MM-DD HH:mm:ss"),
    end: endDate || dayjs().format("YYYY-MM-DD HH:mm:ss"),
  };
  next();
};

// 订单统计
router.get("/orders", handleDateRange, async (req, res) => {
  const { start, end } = req.dateCondition;
  // console.log(start, end);
  const stats = (
    await db.query(
      `
    SELECT 
      COUNT(*) AS total,
      SUM(CASE WHEN status = '已支付' THEN 1 ELSE 0 END) AS paid,
      SUM(CASE WHEN status = '未支付' THEN 1 ELSE 0 END) AS unpaid
    FROM orders`,
      {
        replacements: [start, end],
        type: QueryTypes.SELECT,
      }
    )
  )[0];
  // console.log(stats[0][0], "states");
  const trend = await db.query(
    `
    SELECT
      DATE(order_time) AS date,
      COUNT(*) AS count
    FROM orders
    GROUP BY date(order_time)
    ORDER BY date ASC
  `,
    {
      replacements: [start, end],
      type: QueryTypes.SELECT,
    }
  );
  // console.log(stats[0].total, "1233323221231");
  res.json({
    total: stats[0].total,
    statusDistribution: [
      { status: "已支付", count: stats[0].paid },
      { status: "未支付", count: stats[0].unpaid },
      //   { status: '已取消', count: stats[0].canceled }
    ],
    trend: trend[0],
  });
});
// 获取所有信息
router.get("/allfortunes", handleDateRange, async (req, res) => {
  const { start, end } = req.dateCondition;
  // console.log(start, end);
        const [stats] = await db.query('select id,username,fortune_type,created_at from user_fortunes ')
        
  res.json({
    // total: stats[0].total,
    data:stats
    // trend: trend[0],
  });
})
// 获取测算管理
router.get("/getcsgl", handleDateRange, async (req, res) => {
  const { start, end } = req.dateCondition;
  // console.log(start, end);
        const [stats] = await db.query('select * from user_fortunes ')
        
  res.json({
    // total: stats[0].total,
    data:stats
    // trend: trend[0],
  });
})

// 每日营收

router.get("/getys",async(req,res)=>{
 const result=(
  await db.query(`
    SELECT 
    DATE(order_time) AS data,
    COUNT(*) AS count,
    SUM(amount) AS price
FROM orders
WHERE status = '已支付'
GROUP BY data
ORDER BY data;
    `)
 )
 console.log(result[0])
 res.json({
  data: result[0]
  
});
})

// 测算统计
router.get("/fortunes", handleDateRange, async (req, res) => {
  const { start, end } = req.dateCondition;

  const result = (
    await db.query(
      `
    SELECT
      fortune_type,
      COUNT(*) as count
    FROM user_fortunes
    GROUP BY fortune_type
  `,
      {
        replacements: [start, end],
        type: QueryTypes.SELECT,
      }
    )
  )[0];
  // console.log(result);
  const trend = await db.query(
    `
    SELECT
      DATE(order_time) AS date,
      COUNT(*) AS count
    FROM orders
    GROUP BY date(order_time)
    ORDER BY date ASC
  `,
    {
      replacements: [start, end],
      type: QueryTypes.SELECT,
    }
  );

  res.json({
    total: result.reduce((sum, item) => sum + item.count, 0),
    typeDistribution: result,
    trend: trend[0],
  });
});

// 用户统计
router.get("/users", handleDateRange, async (req, res) => {
  const { start, end } = req.dateCondition;

  const [total, active,gender, age] = await Promise.all([
    // 总用户数
    db.query(`SELECT COUNT(*) AS count FROM user_fortunes`, {
      replacements: [end],
      type: QueryTypes.SELECT,
    }),

    // 活跃用户（最近7天有登录）
    db.query(
      `SELECT COUNT(DISTINCT id) AS count 
      FROM (SELECT id FROM user_fortunes 
            UNION ALL SELECT id FROM user_fortunes) AS activity`,
      {
        replacements: [
          dayjs(end).subtract(7, "days").format("YYYY-MM-DD"),
          end,
        ],
        type: QueryTypes.SELECT,
      }
    ),

    // 性别分布
    db.query(`SELECT sex, COUNT(*) AS count FROM user_fortunes GROUP BY sex`, {
      type: QueryTypes.SELECT,
    }),

    // 年龄分布（基于出生日期计算）
    db.query(
      `
      SELECT 
        CASE
       WHEN YEAR(CURDATE()) - birth_year BETWEEN 18 AND 24 THEN '18-24'
    WHEN YEAR(CURDATE()) - birth_year BETWEEN 25 AND 30 THEN '25-30'
    WHEN YEAR(CURDATE()) - birth_year BETWEEN 31 AND 35 THEN '31-35'
    WHEN YEAR(CURDATE()) - birth_year BETWEEN 36 AND 40 THEN '36-40'
          ELSE '40+'
        END AS ageRange,
        COUNT(*) AS count
      FROM user_fortunes
      GROUP BY ageRange
    `,
      { type: QueryTypes.SELECT }
    ),

    // 地域分布
    // db.query(
    //   `
    //   SELECT
    //     CONCAT('中国-', province) AS region,
    //     COUNT(*) AS count
    //   FROM users
    //   GROUP BY province
    // `,
    //   { type: QueryTypes.SELECT }
    // ),
  ]);
  console.log(gender[0],'123213113')
  res.json({
    total: total[0][0].count,
    activeUsers: active[0][0].count,
    genderDistribution: gender[0],
    ageDistribution: age[0],
    // regionDistribution: region,
    trend: (await getUserTrend(start, end))[0],
  });
});

// 获取用户增长趋势
async function getUserTrend(start, end) {
  return db.query(
    `
    SELECT
      DATE(created_at) AS date,
      COUNT(*) AS count
    FROM user_fortunes
    GROUP BY DATE(created_at)
    ORDER BY date ASC
  `,
    {
      replacements: [start, end],
      type: QueryTypes.SELECT,
    }
  );
}

module.exports = router;
